use audio_db
--DROP PROCEDURE sp_AUDIO_GetCategory
--drop procedure sp_AUDIO_GetCategoryArticleByArticleID
--drop procedure sp_AUDIO_GetArticleByCategoryID
--drop procedure sp_AUDIO_GetArticleByID
--drop procedure sp_AUDIO_GetArticleByPage
--drop procedure sp_AUDIO_SearchArticle
--drop procedure sp_AUDIO_GetFavoriteArticle
--drop procedure sp_AUDIO_Visited_Add
--drop procedure sp_AUDIO_GetArticlePart
--drop procedure sp_AUDIO_GetArticlePartByPage




CREATE PROCEDURE [dbo].[sp_AUDIO_GetCategory]
	@parentID int
AS
	select Cat.CategoryID, Cat.CategoryName, Cat.HasImage,
		(select count(CategoryID) from Category as Cat2 where Cat2.ParentID = Cat.CategoryID AND Cat2.Actived = 1) as CategoryCounter,
		(select count(CategoryID) from CategoryArticle as CatA 
		    Inner Join Article as Art on CatA.ArticleID = Art.ArticleID
		    where 1 = 1
			    And Cat.CategoryID = CatA.CategoryID
			    And Art.Actived = 1
			    And Art.Deleted = 0) as ArticleCounter
	From Category as Cat
	Where Cat.ParentID = @parentID AND Cat.Actived = 1
	Order by Cat.SortOrder

	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetCategoryArticleByArticleID]
	@articleID int
AS
    select CatA.CategoryArticleID, Cat.CategoryID, Cat.CategoryName, CatA.SortOrder
    From Category as Cat
	Inner Join CategoryArticle as CatA on CatA.CategoryID = Cat.CategoryID
	Where CatA.ArticleID = @articleID


	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticleByCategoryID]
	@CategoryID int
AS
	select A.*, C.SortOrder
	from Article as A
	Inner Join CategoryArticle as C on A.ArticleID = C.ArticleID
	Where C.CategoryID = @CategoryID And A.Deleted = 0
	Order by C.SortOrder desc



	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticleByID]
	@articleID int
AS
	select Art.ArticleID, Art.ArticleName, Art.Description, Art.HasImage
	from Article as Art
	Where Art.ArticleID = @articleID

	
	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticleByPage]
    @accountID int,
	@categoryID int,
	@itemTypeID int,
	@pageIndex int,
	@pageSize int
AS
		
	--Select counter
	select count(CatA.ArticleID) as Counter
	from CategoryArticle as CatA
	Inner Join Article as Art on Art.ArticleID = CatA.ArticleID
	Where 1 = 1
		AND Art.Actived = 1
		AND Art.Deleted = 0
		AND CatA.CategoryID = @categoryID
	
	--Paging result		
	declare @startRow int
	declare @endRow int
	set @startRow = @PageSize * @PageIndex
	set @endRow = @StartRow + @PageSize
	select Tmp.ArticleID, Tmp.ArticleName, Tmp.Description, Tmp.HasImage, Tmp.RatePoint, Tmp.PartCounter, Tmp.LastTimeUpdate, Visited
	from 
		(select row_number() over (Order By CatA.SortOrder desc) as rowIndex, Art.*, 
		(select count(ArticlePartID) from ArticlePart as ArtP where ArtP.ArticleID = Art.ArticleID and ArtP.FileStatus = 3) as PartCounter,
		(select count(VisitedID) from Visited as Vis where Vis.AccountID = @accountID and Vis.ItemID = Art.ArticleID and Vis.ItemTypeID = @ItemTypeID) as Visited --Item = 1 is Article type
		from Article as Art
		Inner Join CategoryArticle as CatA on CatA.ArticleID = Art.ArticleID
		Where 1 = 1
			AND Art.Actived = 1
			AND Art.Deleted = 0
			AND CatA.CategoryID = @categoryID) as Tmp
	where (rowIndex > @startRow and rowIndex <= @endRow)

	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetTopArticle]
    @accountID int,
	@itemTypeID int,
	@numberItem int
AS	
	select top (@numberItem) Tmp.ArticleID, Tmp.ArticleName, Tmp.Description, Tmp.HasImage, Tmp.RatePoint, Tmp.PartCounter, Tmp.LastTimeUpdate, Visited
	from 
		(select Art.*, 
		(select count(ArticlePartID) from ArticlePart as ArtP where ArtP.ArticleID = Art.ArticleID and ArtP.FileStatus = 3) as PartCounter,
		(select count(VisitedID) from Visited as Vis where Vis.AccountID = 1 and Vis.ItemID = Art.ArticleID and Vis.ItemTypeID = 2) as Visited --Item = 1 is Article type
		from Article as Art
		Inner Join CategoryArticle as CatA on CatA.ArticleID = Art.ArticleID
		Where 1 = 1
			AND Art.Actived = 1
			AND Art.Deleted = 0
			AND Art.IsHot = 1) as Tmp	
	
	
CREATE PROCEDURE [dbo].[sp_AUDIO_SearchArticle]
    @accountID int,
	@itemTypeID int,
	@keyWord nvarchar(200),
	@pageIndex int,
	@pageSize int
AS
	declare @space char(1)
	
	select count(ArticleID) as Counter
    from Article as Art
	Where 1 = 1
		AND Art.Actived = 1
		AND Art.Deleted = 0
		AND	(1 <> 1
			 or (Art.ArticleName like ('%' + @keyWord + '%'))
			 or (Art.Artist like ('%' + @keyWord + '%'))
			 or (Art.Author like ('%' + @keyWord + '%')))
	
	declare @startRow int
	declare @endRow int
	set @startRow = @PageSize * @PageIndex
	set @endRow = @StartRow + @PageSize
	select Tmp.ArticleID, Tmp.ArticleName, Tmp.Description, Tmp.HasImage, Tmp.RatePoint, Tmp.PartCounter
	from 
		(select row_number() over (Order By Art.LastTimeUpdate desc) as rowIndex, Art.*,
		(select count(ArticlePartID) from ArticlePart as ArtP where ArtP.ArticleID = Art.ArticleID) as PartCounter,
		(select count(VisitedID) from Visited as Vis where  Vis.AccountID = @accountID and Vis.ItemID = Art.ArticleID and Vis.ItemTypeID = @itemTypeID) as Visited --Item = 1 is Article type
		from Article as Art
		Where 1 = 1
			AND Art.Actived = 1
			AND Art.Deleted = 0
		    AND	(1 <> 1
			 or (Art.ArticleName like ('%' + @keyWord + '%'))
			 or (Art.Artist like ('%' + @keyWord + '%'))
			 or (Art.Author like ('%' + @keyWord + '%')))) as Tmp
	where (rowIndex > @startRow and rowIndex <= @endRow)	
	


CREATE PROCEDURE [dbo].[sp_AUDIO_GetFavoriteArticle]
	@accountID int,
	@itemTypeID int,
	@pageIndex int,
	@pageSize int
AS
    --Select counter
	select count(Art.ArticleID) as Counter
	FROM Article as Art
	Inner Join Favorite as Fav on Fav.ItemID = Art.ArticleID 
	Where 1 = 1
		AND Art.Actived = 1
		AND Art.Deleted = 0
		AND Fav.AccountID = @accountID
		AND Fav.ItemTypeID = @itemTypeID
	
	--Paging result		
	declare @startRow int
	declare @endRow int
	set @startRow = @PageSize * @PageIndex
	set @endRow = @StartRow + @PageSize
	select Tmp.ArticleID, Tmp.ArticleName, Tmp.Description, Tmp.HasImage, Tmp.RatePoint, Tmp.PartCounter, Tmp.Visited
	from 
		(select row_number() over (Order By Fav.CreatedDateTime desc) as rowIndex, Art.*,
		(select count(ArticlePartID) from ArticlePart as ArtP where ArtP.ArticleID = Art.ArticleID) as PartCounter,
		1 as Visited --In favorite item, the visited field alway match as true
		FROM Article as Art
		Inner Join Favorite as Fav on Fav.ItemID = Art.ArticleID
		Where 1 = 1
			AND Art.Actived = 1
			AND Art.Deleted = 0
			AND Fav.AccountID = @accountID
			AND Fav.ItemTypeID = @itemTypeID) as Tmp
	where (rowIndex > @startRow and rowIndex <= @endRow)


	
	
CREATE PROCEDURE [dbo].[sp_AUDIO_Visited_Add]
    @accountID int,
	@itemID int,
	@itemTypeID int
AS

IF NOT EXISTS (SELECT 1 FROM [Visited] where [AccountID] = @accountID AND [ItemID] = @itemID AND [ItemTypeID] = @itemTypeID)
BEGIN
INSERT INTO [dbo].[Visited]
           ([AccountID]
           ,[ItemID]
		   ,[ItemTypeID])
     VALUES
           (@accountID
           ,@itemID
		   ,@itemTypeID)
END




	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticlePart]
	@status int
AS
	select AP.* from ArticlePart as AP
	Inner Join Article as A on A.ArticleID = AP.ArticleID
	where AP.Actived = @status
	
	
	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticlePartByID]
	@accountID int,
	@articlePartID int
AS
     select ArtP.*        
          FROM ArticlePart as ArtP 
          WHERE ArtP.ArticlePartID = @articlePartID
	
	
CREATE PROCEDURE [dbo].[sp_AUDIO_GetArticlePartByPage]
    @accountID int,
	@articleID int,
	@itemTypeID int,
	@pageIndex int,
	@pageSize int
AS
	-- select counter
	select count(ArtP.ArticlePartID) as Counter
	from ArticlePart as ArtP
	Where 1 = 1 
		AND ArtP.ArticleID = @articleID 

	-- select article part	
	declare @startRow int
	declare @endRow int
	set @startRow = @PageSize * @PageIndex
	set @endRow = @StartRow + @PageSize
	select Tmp.ArticlePartID, Tmp.ArticlePartName, Tmp.Duration,Tmp.PartCounter, Tmp.FileSize, Visited
	from 
		(select row_number() over (Order By ArtP.SortOrder) as rowIndex, ArtP.*,
		(select count(VisitedID) from Visited as Vis where Vis.AccountID = @accountID and Vis.ItemID = ArtP.ArticlePartID and Vis.ItemTypeID = @itemTypeID) as Visited
		from ArticlePart as ArtP
		Where 1 = 1 
			AND ArtP.ArticleID = @articleID 
			AND ArtP.FileStatus = 3) as Tmp
	where (rowIndex > @startRow and rowIndex <= @endRow)	
	
	
CREATE VIEW [dbo].[VIEW_AUDIO_Article]
AS
SELECT     dbo.Article.ArticleID, dbo.Article.ArticleName, dbo.Article.Description,
                      dbo.Article.HasImage,dbo.Article.Artist, dbo.Article.Author, dbo.Article.PlayCounter, 
                      dbo.Article.Actived, dbo.Article.LastTimeUpdate, dbo.Article.CreatedDateTime, dbo.Article.RateTimes, dbo.Article.RatePoint, 
                      dbo.Article.Deleted, dbo.CategoryArticle.CategoryID, dbo.CategoryArticle.SortOrder
FROM         dbo.Article INNER JOIN
                      dbo.CategoryArticle ON dbo.Article.ArticleID = dbo.CategoryArticle.ArticleID	